﻿using IBatisNet.Common.Utilities;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Commands;
using IBatisNet.DataMapper.Configuration.Statements;
using IBatisNet.DataMapper.MappedStatements;
using IBatisNet.DataMapper.Scope;
using IBatisNet.DataMapper.SessionStore;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using Yunzuan.Foundation.Common;
using Yunzuan.Foundation.Configurations;
using Yunzuan.Foundation.Web;
using Yunzuan.Models.Entities;
using YunZuan.Models.Attributes;
using YunZuan.Models.Entities;

namespace Yunzuan.Foundation.DataAccess
{
    /// <summary>
    /// 数据库服务基类
    /// </summary>
    public abstract class DataContextBase : IDataContextBase
    {
        //待优化内容：
        // - 抽象各个数据库方言部分

        private static readonly string LOG_FILE_PATH = AppDomain.CurrentDomain.BaseDirectory + "\\Yunzuan.log";

        #region IBatis.Net

        protected ISqlMapSession session = null;
        protected ISqlMapper mapper = null;
        private ISqlMapper instance = null;
        private static readonly object lockHelper = new object();
        private string configFileName = string.Empty;

        private void Reset(object obj)
        {
            lock (lockHelper)
            {
                try
                {
                    using (StreamWriter sw = new StreamWriter(LOG_FILE_PATH, true, Encoding.UTF8))
                    {
                        sw.WriteLine("Reload" + DateTime.Now.ToString());
                    }
                    instance = null;
                    this.SetDB(this.configFileName);
                }
                catch
                {

                }
            }
        }

        protected DataContextBase(string configName)
        {
            this.SetDB(configName);
        }

        private ISqlMapper CreateMapper(string configName)
        {
            IBatisNet.DataMapper.Configuration.DomSqlMapBuilder config = new IBatisNet.DataMapper.Configuration.DomSqlMapBuilder();
            ConfigureHandler handler = new ConfigureHandler(Reset);
            ISqlMapper instance = config.ConfigureAndWatch(IBatisConfig.SQL_MAP_CONFIG_PATH + (string.IsNullOrEmpty(configName) ? "sqlMap.config" : configName + ".db.config"), handler);
            instance.SessionStore = new HybridWebThreadSessionStore(instance.Id);
            return instance;
        }

        private void SetDB(string configName)
        {
            this.configFileName = configName;
            if (instance == null)
            {
                lock (lockHelper)
                {
                    if (instance == null)
                    {
                        try
                        {
                            //using (StreamWriter sw = new StreamWriter(LOG_FILE_PATH, true, Encoding.UTF8))
                            //{
                            //    sw.WriteLine("Init[" + configName + "]" + DateTime.Now.ToString());
                            //}
                            IBatisNet.DataMapper.Configuration.DomSqlMapBuilder config = new IBatisNet.DataMapper.Configuration.DomSqlMapBuilder();
                            ConfigureHandler handler = new ConfigureHandler(Reset);
                            config.ValidateSqlMapConfig = false;
                            instance = config.ConfigureAndWatch(IBatisConfig.SQL_MAP_CONFIG_PATH + (string.IsNullOrEmpty(configName) ? "sqlMap.config" : "sqlMap.config"), handler);
                            instance.SessionStore = new IBatisNet.DataMapper.SessionStore.HybridWebThreadSessionStore(instance.Id);
                            reGetISqlMapper(ref instance, configName);
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                }
            }
            else
            {
                reGetISqlMapper(ref instance, configName);
            }
        }
        static string userName = ConfigurationManager.AppSettings["DBLoginName"];
        static string userPwd = ConfigurationManager.AppSettings["DBLoginPwd"];
        public void reGetISqlMapper(ref ISqlMapper instance, string key)
        {
            string connectionStr = "Server=localhost;Database={0};Uid={1};Pwd={2};convert zero datetime =true;allow zero datetime=true";
            if(key== "SupplierDB")
                instance.DataSource.ConnectionString = string.Format(connectionStr, GlobalConfig.SupplierDB, userName, userPwd);
            else
                instance.DataSource.ConnectionString = string.Format(connectionStr, (ConfigurationManager.GetSection(key) as ConfigSettings).DbKey, userName, userPwd);
        }


        protected ISqlMapper GetMapper()
        {
            return instance;
        }

        protected ISqlMapSession GetSession()
        {
            ISqlMapSession result = null;
            if (session != null)
            {

                result = session;
            }
            else if (mapper.LocalSession != null)
            {
                result = mapper.LocalSession;
            }
            else
            {
                result = mapper.CreateSqlMapSession();
            }

            if (result.Connection == null)
            {
                result.OpenConnection();
            }
            return result;
        }

        #endregion

        #region Extend

        /// <summary>
        /// 根据传入对象的属性查询相应实体是否存在
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool IsExist(BaseEntity entity)
        {
            string tableName = string.Empty;
            Type t = entity.GetType();
            object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
            if (attributes.Length > 0)
            {
                TableAttribute attr = attributes[0] as TableAttribute;
                tableName = attr.TableName;
            }

            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);

            StringBuilder builder = new StringBuilder();
            //反射获取所有属性
            IEnumerable<ColumnInfo> columns = entity.GetChangedColumn();
            foreach (ColumnInfo item in columns)
            {
                if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
                {
                    builder.Append((builder.Length == 0 ? "" : " AND ") + "[" + item.ColumnName + "] = @" + item.ColumnName);

                }
                else if (mapper.DataSource.DbProvider.Name == "MySql")
                {
                    builder.Append((builder.Length == 0 ? "" : " AND ") + "`" + item.ColumnName + "` = ?");
                }
                else
                {
                    builder.Append((builder.Length == 0 ? "" : " AND ") + "[" + item.ColumnName + "] = @" + item.ColumnName);
                }

                IDbDataParameter para = sqlMapSession.CreateDataParameter();
                para.ParameterName = item.ColumnName;
                para.Value = item.ColumnValue;
                cmd.Parameters.Add(para);
            }
            if (cmd.Parameters.Count == 0)
            {
                return false;
            }

            //FIX
            string sql = string.Empty;
            if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
            {
                sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + builder.ToString();

            }
            else if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + builder.ToString();
            }
            else
            {
                sql = "SELECT COUNT(*) FROM " + tableName + "(NOLOCK) WHERE " + builder.ToString();
            }
            cmd.CommandText = sql;

            try
            {
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }

                object tmpResult = cmd.ExecuteScalar();
                int result = Convert.ToInt32(tmpResult);
                return result > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 通过实体主键返回单条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public T Get<T>(BaseEntity entity) where T : BaseEntity, new()
        {
            string primaryKey = string.Empty;
            string tableName = string.Empty;
            Type t = entity.GetType();
            object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
            if (attributes.Length > 0)
            {
                TableAttribute attr = attributes[0] as TableAttribute;
                primaryKey = attr.PrimaryKey;
                tableName = attr.TableName;
            }
            if (string.IsNullOrEmpty(primaryKey))
            {
                throw new Exception("实体类没有标识主键字段");
            }

            object primaryKeyValue = t.GetProperty(primaryKey).GetValue(entity, null);

            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);

            IDbDataParameter paraPrimary = sqlMapSession.CreateDataParameter();
            paraPrimary.ParameterName = primaryKey;
            paraPrimary.Value = primaryKeyValue;
            cmd.Parameters.Add(paraPrimary);
            //FIX
            string sql = string.Empty;

            if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
            {
                sql = "SELECT TOP 1 * FROM " + tableName + " WHERE " + primaryKey + " = ?";
            }
            else if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                sql = "SELECT * FROM " + tableName + " WHERE " + primaryKey + " = ? LIMIT 1";
            }
            else
            {
                sql = "SELECT TOP 1 * FROM " + tableName + "(NOLOCK) WHERE " + primaryKey + " = @" + primaryKey;
            }

            cmd.CommandText = sql;

            try
            {
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return this.ConvertToObject<T>(reader);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 获取单条数据
        /// </summary>
        /// <remarks>
        /// 支持OleDb2.0, MySql, SQLServer
        /// </remarks>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Get<T>(int? id) where T : BaseEntity, new()
        {
            if (!id.HasValue)
            {
                return null;
            }
            string primaryKey = string.Empty;
            string tableName = string.Empty;
            Type t = typeof(T);
            object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
            if (attributes.Length > 0)
            {
                TableAttribute attr = attributes[0] as TableAttribute;
                primaryKey = attr.PrimaryKey;
                tableName = attr.TableName;
            }
            if (string.IsNullOrEmpty(primaryKey))
            {
                throw new Exception("实体类没有标识主键字段");
            }

            object primaryKeyValue = id;

            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);

            IDbDataParameter paraPrimary = sqlMapSession.CreateDataParameter();
            paraPrimary.ParameterName = primaryKey;
            paraPrimary.Value = primaryKeyValue;
            cmd.Parameters.Add(paraPrimary);
            //FIX
            string sql = string.Empty;

            if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
            {
                sql = "SELECT TOP 1 * FROM " + tableName + " WHERE " + primaryKey + " = ?";
            }
            else if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                sql = "SELECT * FROM " + tableName + " WHERE " + primaryKey + " = ? LIMIT 1";
            }
            else
            {
                sql = "SELECT TOP 1 * FROM " + tableName + "(NOLOCK) WHERE " + primaryKey + " = @" + primaryKey;
            }

            cmd.CommandText = sql;

            try
            {
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return this.ConvertToObject<T>(reader);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 查询单条数据
        /// </summary>
        /// <typeparam name="T">指定表实体</typeparam>
        /// <param name="id">主键值</param>
        /// <returns>HashResult</returns>
        public HashResult GetHashResult<T>(int? id) where T : BaseEntity
        {
            if (!id.HasValue)
            {
                return null;
            }
            string primaryKey = string.Empty;
            string tableName = string.Empty;
            Type t = typeof(T);
            object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
            if (attributes.Length > 0)
            {
                TableAttribute attr = attributes[0] as TableAttribute;
                primaryKey = attr.PrimaryKey;
                tableName = attr.TableName;
            }
            if (string.IsNullOrEmpty(primaryKey))
            {
                throw new Exception("实体类没有标识主键字段");
            }

            object primaryKeyValue = id;

            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);

            IDbDataParameter paraPrimary = sqlMapSession.CreateDataParameter();
            paraPrimary.ParameterName = primaryKey;
            paraPrimary.Value = primaryKeyValue;
            cmd.Parameters.Add(paraPrimary);

            //FIX
            string sql = string.Empty;

            if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
            {
                sql = "SELECT TOP 1 * FROM " + tableName + " WHERE " + primaryKey + " = ?";
            }
            else if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                sql = "SELECT * FROM " + tableName + " WHERE " + primaryKey + " = ? LIMIT 1";
            }
            else
            {
                sql = "SELECT TOP 1 * FROM " + tableName + "(NOLOCK) WHERE " + primaryKey + " = @" + primaryKey;
            }


            cmd.CommandText = sql;

            try
            {
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return this.ConvertToHashResult(reader);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Add(BaseEntity entity)
        {
            return this.Add(entity, null);
        }

        /// <summary>
        /// 添加一条数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="excludeColumns"></param>
        /// <returns></returns>
        public int Add(BaseEntity entity, params string[] excludeColumns)
        {
            HashSet<string> hsExclude = new HashSet<string>();
            if (excludeColumns != null)
            {
                for (int i = 0; i < excludeColumns.Length; i++)
                {
                    if (!string.IsNullOrEmpty(excludeColumns[i]) && !hsExclude.Contains(excludeColumns[i]))
                    {
                        hsExclude.Add(excludeColumns[i]);
                    }
                }
            }

            string primaryKey = string.Empty;
            string tableName = string.Empty;
            Type t = entity.GetType();
            object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
            if (attributes.Length > 0)
            {
                TableAttribute attr = attributes[0] as TableAttribute;
                primaryKey = attr.PrimaryKey;
                tableName = attr.TableName;
            }

            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);

            StringBuilder builderColumns = new StringBuilder();
            StringBuilder builderValues = new StringBuilder();

            bool hasAutoIncrementColumn = false;
            string autoIncrementColumnName = string.Empty;
            //判断主键是否是自增
            PropertyInfo pk = t.GetProperty(primaryKey);
            if (pk != null)
            {
                object[] columnAttributes = pk.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (columnAttributes.Length > 0)
                {
                    ColumnAttribute attr = columnAttributes[0] as ColumnAttribute;
                    hasAutoIncrementColumn = attr.IsAutoIncrement;
                    autoIncrementColumnName = primaryKey;
                }
            }

            //反射获取所有属性
            IEnumerable<ColumnInfo> columns = entity.GetChangedColumn();
            foreach (ColumnInfo item in columns)
            {
                if (!item.IsAutoIncrement)
                {
                    if (!hsExclude.Contains(item.ColumnName) && item.ColumnValue != null)
                    {
                        if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
                        {
                            builderColumns.Append((builderColumns.Length == 0 ? "" : ", ") + "[" + item.ColumnName + "]");
                            builderValues.Append((builderValues.Length == 0 ? "" : ", ") + "@" + item.ColumnName);
                        }
                        else if (mapper.DataSource.DbProvider.Name == "MySql")
                        {
                            builderColumns.Append((builderColumns.Length == 0 ? "" : ", ") + "`" + item.ColumnName + "`");
                            builderValues.Append((builderValues.Length == 0 ? "" : ", ") + "?");
                        }
                        else
                        {
                            builderColumns.Append((builderColumns.Length == 0 ? "" : ", ") + "[" + item.ColumnName + "]");
                            builderValues.Append((builderValues.Length == 0 ? "" : ", ") + "@" + item.ColumnName);
                        }

                        IDbDataParameter para = sqlMapSession.CreateDataParameter();
                        para.ParameterName = item.ColumnName;
                        para.Value = item.ColumnValue;
                        cmd.Parameters.Add(para);
                    }
                }
                else
                {
                    hasAutoIncrementColumn = true;
                    autoIncrementColumnName = item.ColumnName;
                }
            }

            //FIX
            string providerName = mapper.DataSource.DbProvider.Name;
            string sql = string.Empty;

            if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
            {
                sql = "INSERT INTO " + tableName + " (" + builderColumns.ToString() + ") VALUES (" + builderValues.ToString() + ")";
            }
            else if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                sql = "INSERT INTO " + tableName + " (" + builderColumns.ToString() + ") VALUES (" + builderValues.ToString() + ")";
            }
            else
            {
                sql = "INSERT INTO " + tableName + " (" + builderColumns.ToString() + ") VALUES (" + builderValues.ToString() + ")" + (hasAutoIncrementColumn && providerName != "OleDb2.0" ? ";SELECT @@IDENTITY" : "");
            }



            try
            {
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }
                if (hasAutoIncrementColumn)
                {
                    cmd.CommandText = sql;
                    //有自增字段，同时设置自增字段值
                    object result = cmd.ExecuteScalar();

                    //FIX
                    if (providerName == "OleDb2.0")
                    {
                        cmd.CommandText = "SELECT @@IDENTITY";
                        cmd.Parameters.Clear();
                        result = cmd.ExecuteScalar();
                    }
                    else if (providerName == "MySql")
                    {
                        cmd.CommandText = "SELECT LAST_INSERT_ID()";
                        cmd.Parameters.Clear();
                        result = cmd.ExecuteScalar();
                    }

                    PropertyInfo property = t.GetProperty(autoIncrementColumnName);

                    try
                    {
                        if (property.PropertyType.Name == "Int32")
                        {
                            property.SetValue(entity, Convert.ToInt32(result), null);
                        }
                        else
                        {
                            property.SetValue(entity, Convert.ToInt64(result), null);
                        }
                    }
                    catch (Exception)
                    {
                        property.SetValue(entity, Convert.ToInt32(result), null);
                    }

                    return int.Parse(result.ToString());
                }
                else
                {
                    cmd.CommandText = sql + ";select row_count(); ";
                    return int.Parse(cmd.ExecuteScalar().ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public void Add(List<object> list)
        {
            this.AddList(list);
        }
        public void AddList(List<object> list)
        {
            HashSet<string> hsExclude = new HashSet<string>();
              
            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);

            StringBuilder builderColumns = new StringBuilder();
            StringBuilder builderValues = new StringBuilder();
            string sqlTitle = string.Empty;
            string sqlVal = string.Empty;
            bool hasAutoIncrementColumn = false;
            string autoIncrementColumnName = string.Empty;
            int i = 0;
            string primaryKey = string.Empty;
            string tableName = string.Empty;
            foreach (var tEntity in list)
            {
                Type t = tEntity.GetType();
                if (i == 0) //只需要第一次取值
                {
                    object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
                    if (attributes.Length > 0)
                    {
                        TableAttribute attr = attributes[0] as TableAttribute;
                        primaryKey = attr.PrimaryKey;
                        tableName = attr.TableName;
                    }

                    //判断主键是否是自增
                    PropertyInfo pk = t.GetProperty(primaryKey);
                    if (pk != null)
                    {
                        object[] columnAttributes = pk.GetCustomAttributes(typeof(ColumnAttribute), true);
                        if (columnAttributes.Length > 0)
                        {
                            ColumnAttribute attr = columnAttributes[0] as ColumnAttribute;
                            hasAutoIncrementColumn = attr.IsAutoIncrement;
                            autoIncrementColumnName = primaryKey;
                        }
                    }
                }
                //反射获取所有属性
                builderValues.Clear();
                PropertyInfo[] columns = t.GetProperties();
                foreach (PropertyInfo item in columns)
                {
                    if (primaryKey != item.Name)// 不是主键
                    {
                        if (i == 0) //只需要第一次取值
                        {
                            builderColumns.Append((builderColumns.Length == 0 ? "" : ", ") + "`" + item.Name + "`");
                        }
                        if (item.PropertyType.FullName == typeof(DateTime).FullName)
                        {
                            DateTime pValue = (DateTime)item.GetValue(tEntity, null);
                            if (pValue != null && pValue != DateTime.MinValue)    //dateTime类型申明时默认值为最小值
                            {
                                builderValues.Append((builderValues.Length == 0 ? "" : ", ") + pValue);
                            }
                            else
                            {
                                builderValues.Append((builderValues.Length == 0 ? "" : ", ") + "NULL");
                            }
                        }
                        else if (item.PropertyType.FullName.Contains(typeof(Int32).FullName))
                        {
                            int pValue = (int)item.GetValue(tEntity, null);
                            builderValues.Append((builderValues.Length == 0 ? "" : ", ") + pValue);
                        }
                        else if (item.PropertyType.FullName == typeof(Boolean).FullName)
                        {
                            Object pValue = item.GetValue(tEntity, null);
                            builderValues.Append((builderValues.Length == 0 ? "" : ", ") + pValue);
                        }
                        else if (item.PropertyType.FullName == typeof(String).FullName)
                        {
                            Object pValue = item.GetValue(tEntity, null);
                            if (string.IsNullOrEmpty(pValue.ToString()))
                                builderValues.Append((builderValues.Length == 0 ? "" : ", ") + "''");
                            else
                                builderValues.Append((builderValues.Length == 0 ? "" : ", ") + "'" + pValue + "'");
                        }
                    }
                }
                if (i == 0) //只需要第一次取值
                {
                    sqlTitle = $"INSERT INTO {tableName} ({builderColumns.ToString()})VALUES ";
                    i++;
                }
                sqlVal += $"{(sqlVal.Length == 0 ? "" : ", ") }({ builderValues.ToString()})";

            }
            try
            {
                string sql = (sqlTitle + sqlVal);
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }
                if (hasAutoIncrementColumn)
                {
                    cmd.CommandText = sql;
                    //有自增字段，同时设置自增字段值
                    object result = cmd.ExecuteScalar();

                }
                else
                {
                    cmd.CommandText = sql + ";select row_count(); ";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Delete(BaseEntity entity)
        {
            string primaryKey = string.Empty;
            string tableName = string.Empty;
            Type t = entity.GetType();
            object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
            if (attributes.Length > 0)
            {
                TableAttribute attr = attributes[0] as TableAttribute;
                primaryKey = attr.PrimaryKey;
                tableName = attr.TableName;
            }
            if (string.IsNullOrEmpty(primaryKey))
            {
                throw new Exception("实体类没有标识主键字段");
            }

            object primaryKeyValue = t.GetProperty(primaryKey).GetValue(entity, null);

            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);

            IDbDataParameter paraPrimary = sqlMapSession.CreateDataParameter();
            paraPrimary.ParameterName = primaryKey;
            paraPrimary.Value = primaryKeyValue;
            cmd.Parameters.Add(paraPrimary);

            //FIX
            string sql = string.Empty;

            if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
            {
                sql = "DELETE FROM " + tableName + " WHERE " + primaryKey + " = ?";
            }
            else if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                sql = "DELETE FROM " + tableName + " WHERE " + primaryKey + " = ?";
            }
            else
            {
                sql = "DELETE " + tableName + " WHERE " + primaryKey + " = @" + primaryKey;
            }

            cmd.CommandText = sql;

            try
            {
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Edit(BaseEntity entity)
        {
            return this.Edit(entity, null);
        }

        public int Edit(BaseEntity entity, params string[] excludeColumns)
        {
            HashSet<string> hsExclude = new HashSet<string>();
            if (excludeColumns != null)
            {
                for (int i = 0; i < excludeColumns.Length; i++)
                {
                    if (!string.IsNullOrEmpty(excludeColumns[i]) && !hsExclude.Contains(excludeColumns[i]))
                    {
                        hsExclude.Add(excludeColumns[i]);
                    }
                }
            }

            string primaryKey = string.Empty;
            string tableName = string.Empty;
            Type t = entity.GetType();
            object[] attributes = t.GetCustomAttributes(typeof(TableAttribute), true);
            if (attributes.Length > 0)
            {
                TableAttribute attr = attributes[0] as TableAttribute;
                primaryKey = attr.PrimaryKey;
                tableName = attr.TableName;
            }
            if (string.IsNullOrEmpty(primaryKey))
            {
                throw new Exception("实体类没有标识主键字段");
            }

            object primaryKeyValue = t.GetProperty(primaryKey).GetValue(entity, null);

            ISqlMapSession sqlMapSession = this.GetSession();
            IDbCommand cmd = sqlMapSession.CreateCommand(System.Data.CommandType.Text);
            StringBuilder builderColumns = new StringBuilder();

            //反射获取所有属性
            IEnumerable<ColumnInfo> columns = entity.GetChangedColumn();
            foreach (ColumnInfo item in columns)
            {
                if (!item.IsAutoIncrement)
                {
                    if (!hsExclude.Contains(item.ColumnName))
                    {
                        if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
                        {
                            builderColumns.Append((builderColumns.Length == 0 ? "" : ", ") + "[" + item.ColumnName + "] = @" + item.ColumnName);
                        }
                        else if (mapper.DataSource.DbProvider.Name == "MySql")
                        {
                            builderColumns.Append((builderColumns.Length == 0 ? "" : ", ") + "`" + item.ColumnName + "` = ?");
                        }
                        else
                        {
                            builderColumns.Append((builderColumns.Length == 0 ? "" : ", ") + "[" + item.ColumnName + "] = @" + item.ColumnName);
                        }

                        IDbDataParameter para = sqlMapSession.CreateDataParameter();
                        para.ParameterName = item.ColumnName;
                        para.Value = item.ColumnValue;
                        cmd.Parameters.Add(para);
                    }
                }
            }

            IDbDataParameter paraPrimary = sqlMapSession.CreateDataParameter();
            paraPrimary.ParameterName = "PRIMARYKEY_" + primaryKey;
            paraPrimary.Value = primaryKeyValue;
            cmd.Parameters.Add(paraPrimary);

            string sql = string.Empty;

            if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
            {
                sql = "UPDATE " + tableName + " SET " + builderColumns.ToString() + " WHERE " + primaryKey + " = @PRIMARYKEY_" + primaryKey;
            }
            else if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                sql = "UPDATE " + tableName + " SET " + builderColumns.ToString() + " WHERE " + primaryKey + " = ?";
            }
            else
            {
                sql = "UPDATE " + tableName + " SET " + builderColumns.ToString() + " WHERE " + primaryKey + " = @PRIMARYKEY_" + primaryKey;
            }

            cmd.CommandText = sql;

            try
            {
                cmd.Connection = sqlMapSession.Connection;
                if (sqlMapSession.Connection.State == ConnectionState.Closed)
                {
                    sqlMapSession.Connection.Open();
                }
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (!sqlMapSession.IsTransactionStart && cmd.Connection != null && (cmd.Connection.State != ConnectionState.Fetching && cmd.Connection.State != ConnectionState.Executing))
                {
                    cmd.Connection.Close();
                }
            }
        }

        #endregion

        public object Insert(string statementName, object parameterObject)
        {
            return mapper.Insert(statementName, parameterObject);
        }

        public int Delete(string statementName, object parameterObject)
        {
            return mapper.Delete(statementName, parameterObject);
        }

        public int Update(string statementName, object parameterObject)
        {
            return mapper.Update(statementName, parameterObject);
        }

        public IDictionary<K, V> QueryForDictionary<K, V>(string statementName, object parameterObject, string keyProperty)
        {
            return mapper.QueryForDictionary<K, V>(statementName, parameterObject, keyProperty);
        }

        public System.Collections.IDictionary QueryForDictionary(string statementName, object parameterObject, string keyProperty)
        {
            return mapper.QueryForDictionary(statementName, parameterObject, keyProperty);
        }

        public IDictionary<K, V> QueryForDictionary<K, V>(string statementName, object parameterObject, string keyProperty, string valueProperty)
        {
            return mapper.QueryForDictionary<K, V>(statementName, parameterObject, keyProperty, valueProperty);
        }

        public System.Collections.IDictionary QueryForDictionary(string statementName, object parameterObject, string keyProperty, string valueProperty)
        {
            return mapper.QueryForDictionary(statementName, parameterObject, keyProperty, valueProperty);
        }

        public System.Collections.IList QueryForList(string statementName, object parameterObject)
        {
            return mapper.QueryForList(statementName, parameterObject);
        }

        public IList<T> QueryForList<T>(string statementName, object parameterObject)
        {
            return mapper.QueryForList<T>(statementName, parameterObject);
        }

        public void QueryForList<T>(string statementName, object parameterObject, IList<T> resultObject)
        {
            mapper.QueryForList<T>(statementName, parameterObject, resultObject);
        }

        public void QueryForList(string statementName, object parameterObject, System.Collections.IList resultObject)
        {
            mapper.QueryForList(statementName, parameterObject, resultObject);
        }

        public PageList<T> QueryForList<T>(string statementName, object parameterObject, int pageSize, int pageIndex) where T : class
        {
            return QueryForList<T>(statementName, parameterObject, pageSize, pageIndex, false);
        }

        public int Count(string statementName, object parameterObject)
        {
            bool flag = false;
            ISqlMapSession sqlMapSession = mapper.LocalSession;
            if (sqlMapSession == null)
            {
                sqlMapSession = mapper.CreateSqlMapSession();
                flag = true;
            }
            try
            {
                IMappedStatement mappedStatement = mapper.GetMappedStatement(statementName);
                IStatement statement = mappedStatement.Statement;
                RequestScope request = statement.Sql.GetRequestScope(mappedStatement, parameterObject, sqlMapSession);
                string statementsql = request.PreparedStatement.PreparedSql;

                int count = SqlMapperExtension.GetCount(request, sqlMapSession, this.ConvertToQuickCountSQL(statementsql));
                return count;
            }
            finally
            {
                if (flag)
                {
                    sqlMapSession.CloseConnection();
                }
            }
        }

        public PageList<T> QueryForList<T>(string statementName, object parameterObject, int pageSize, int pageIndex, bool countOnlyFirstPage) where T : class
        {
            bool flag = false;
            ISqlMapSession sqlMapSession = mapper.LocalSession;
            if (sqlMapSession == null)
            {
                sqlMapSession = mapper.CreateSqlMapSession();
                flag = true;
            }
            try
            {
                IMappedStatement mappedStatement = mapper.GetMappedStatement(statementName);
                IStatement statement = mappedStatement.Statement;
                RequestScope request = statement.Sql.GetRequestScope(mappedStatement, parameterObject, sqlMapSession);
                string statementsql = request.PreparedStatement.PreparedSql;
                //拼接查询结果SQL
                string cmdPageSql = this.ConvertToPaginatedSQL(statementsql, pageSize, pageIndex);
                request.IDbCommand = new DbCommandDecorator(sqlMapSession.CreateCommand(statement.CommandType), request);

                SqlMapperExtension.ApplyParameterMap(sqlMapSession, request.IDbCommand, request, statement, parameterObject);
                //拼接总数SQL
                //int totalCount = SqlMapperExtension.GetCount(request, sqlMapSession, this.ConvertToCountSQL(statementsql));
                int totalCount = countOnlyFirstPage && pageIndex > 1 ? 0 : SqlMapperExtension.GetCount(request, sqlMapSession, this.ConvertToQuickCountSQL(statementsql));

                request.IDbCommand.CommandText = cmdPageSql;
                IList<T> list = null;
                if (mapper.DataSource.DbProvider.Name == "OleDb2.0")
                {
                    list = mapper.QueryForList<T>(statementName, parameterObject, (pageSize * (pageIndex - 1)), pageSize);
                }
                else if (mapper.DataSource.DbProvider.Name == "MySql")
                {
                    list = SqlMapperExtension.RunQueryForList<T>(statement, request, sqlMapSession, parameterObject, null, null);
                }
                else
                {
                    list = SqlMapperExtension.RunQueryForList<T>(statement, request, sqlMapSession, parameterObject, null, null);
                }
                PageList<T> result = new PageList<T>();
                result.Data = list;
                result.PageIndex = pageIndex == 0 ? 1 : pageIndex;
                result.PageSize = pageSize;
                result.TotalCount = totalCount;
                if (pageSize == 0)
                {
                    result.PageCount = totalCount;
                }
                else
                {
                    result.PageCount = result.TotalCount % pageSize == 0 ? result.TotalCount / pageSize : (result.TotalCount / pageSize + 1);
                }
                return result;
            }
            finally
            {
                if (flag)
                {
                    sqlMapSession.CloseConnection();
                }
            }
        }

        public PageList<Hashtable> QueryForList(string statementName, object parameterObject, int pageSize, int pageIndex)
        {
            return this.QueryForList<Hashtable>(statementName, parameterObject, pageSize, pageIndex);
        }

        public System.Collections.IDictionary QueryForMap(string statementName, object parameterObject, string keyProperty)
        {
            return mapper.QueryForMap(statementName, parameterObject, keyProperty);
        }

        public System.Collections.IDictionary QueryForMap(string statementName, object parameterObject, string keyProperty, string valueProperty)
        {
            return mapper.QueryForMap(statementName, parameterObject, keyProperty, valueProperty);
        }

        public T QueryForObject<T>(string statementName, object parameterObject)
        {
            return mapper.QueryForObject<T>(statementName, parameterObject);
        }

        public object QueryForObject(string statementName, object parameterObject)
        {
            return mapper.QueryForObject(statementName, parameterObject);
        }

        public object QueryForObject(string statementName, object parameterObject, object resultObject)
        {
            return mapper.QueryForObject(statementName, parameterObject, resultObject);
        }

        public T QueryForObject<T>(string statementName, object parameterObject, T instanceObject)
        {
            return mapper.QueryForObject<T>(statementName, parameterObject, instanceObject);
        }


        #region helper

        protected T ConvertToObject<T>(System.Data.IDataReader reader) where T : BaseEntity
        {
            if (reader.Read())
            {
                try
                {
                    //由于是是未知的类型,所以必须通过Activator.CreateInstance<T>()方法来依据T的类型动态创建数据实体对象
                    T rowInstance = Activator.CreateInstance<T>();
                    //通过反射取得对象所有的Property
                    foreach (PropertyInfo property in typeof(T).GetProperties())
                    {
                        //取得当前数据库字段的顺序
                        int Ordinal = reader.GetOrdinal(property.Name);
                        if (reader.GetValue(Ordinal) != DBNull.Value)
                        {
                            //将DataReader读取出来的数据填充到对象实体的属性里
                            //Property.SetValue(rowInstance, Convert.ChangeType(reader.GetValue(Ordinal), Property.PropertyType), null);
                            property.SetValue(rowInstance, Convert.ChangeType(reader.GetValue(Ordinal), (Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType)), null);
                        }
                    }
                    //将数据实体对象add到泛型集合中
                    return rowInstance;
                }
                finally
                {
                    if (reader != null && !reader.IsClosed)
                    {
                        reader.Close();
                    }
                }
            }
            else
            {
                return default(T);
            }
        }

        protected HashResult ConvertToHashResult(System.Data.IDataReader reader)
        {
            HashResult result = new HashResult();
            if (reader.Read())
            {
                try
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader.GetValue(i));
                    }
                }
                finally
                {
                    if (reader != null && !reader.IsClosed)
                    {
                        reader.Close();
                    }
                }
                result.IsNull = false;
            }
            else
            {
                result.IsNull = true;
            }
            return result;
        }

        protected string ConvertToCountSQL(string sql)
        {
            string temp = sql.ToLower();
            int posOrder = temp.IndexOf(" order ");
            if (posOrder < 0)
            {
                return string.Format("SELECT COUNT(*) AS c FROM ({0}) t ", sql);
            }
            else
            {
                return string.Format("SELECT COUNT(*) AS c FROM ({0}) t ", sql.Substring(0, posOrder));
            }
        }

        protected string ConvertToQuickCountSQL(string sql)
        {
            string temp = sql.ToLower();
            int posOrder = temp.IndexOf(" order ");
            int posFrom = temp.IndexOf(" from ");
            if (posOrder < 0)
            {
                return string.Format("SELECT COUNT(*) FROM {0}", sql.Substring(posFrom + 5));
            }
            else
            {
                return string.Format("SELECT COUNT(*) FROM {0}", sql.Substring(posFrom + 5, posOrder - posFrom - 5));
            }
        }

        protected string ConvertToPaginatedSQL(string sql, int pageSize, int pageIndex)
        {
            string result = sql;

            if (mapper.DataSource.DbProvider.Name == "MySql")
            {
                result += " LIMIT " + (pageSize * (pageIndex - 1)).ToString() + "," + pageSize.ToString();

                return result;
            }
            else
            {
                string between = string.Format(" BETWEEN {0} AND {1}", pageSize * (pageIndex - 1) + 1, pageSize * pageIndex);

                string temp = sql.ToLower();
                int posFrom = temp.IndexOf(" from ");
                int posOrder = temp.IndexOf(" order ");
                string s1 = sql.Substring(0, posFrom);//SELECT -> FROM 之间
                string s2 = posOrder < 0 ? sql.Substring(posFrom) : sql.Substring(posFrom, posOrder - posFrom); //FROM -> ORDER 之间 | FROM 之后

                if (posOrder < 0)
                {
                    result = "SELECT * FROM (SELECT * ,ROW_NUMBER() OVER(ORDER BY __) AS _ FROM (" + s1
                        + ", '' AS __ " + s2 + ") AS T) AS T2 WHERE _ " + between;
                }
                else
                {
                    string s3 = sql.Substring(posOrder); //ORDER 之后
                    result = "SELECT * FROM (" + s1 + " ,ROW_NUMBER() OVER(" + s3 + ") AS _ " + s2
                        + ") AS T WHERE _ " + between;
                }
                return result;
            }
        }

        #endregion

        #region Access

        #endregion


        public IDbCommand CreateCommand()
        {
            ISqlMapSession session = this.mapper.LocalSession;
            if (session == null)
            {
                session = new SqlMapSession(this.mapper);
                if (session.Connection == null)
                {
                    session.CreateConnection();
                }
                if (session.Connection.State != ConnectionState.Open)
                {
                    session.OpenConnection();
                }
            }
            var cmd = session.CreateCommand(CommandType.Text);
            return cmd;
        }

        public IDbCommand CreateCommand(string commandText)
        {
            bool isSessionLocal = false;
            ISqlMapSession session = this.mapper.LocalSession;
            if (session == null)
            {
                session = new SqlMapSession(this.mapper);
                session.OpenConnection();
                isSessionLocal = true;
            }
            var cmd = session.CreateCommand(CommandType.Text);
            cmd.CommandText = commandText;
            cmd.ExecuteNonQuery();

            if (isSessionLocal)
            {
                session.CloseConnection();
            }

            return cmd;
        }
    }
}
